import pandasql as psql
import pandas as pd
df1=pd.DataFrame({"name":["张三","李四","小明"],"rank":range(10,13)})
print(df1)
# locals() or globals() variable environment
psqldf=lambda sql:psql.sqldf(sql,env=globals())
print(psqldf("select * from df1 where name='张三' or name='李四'"))

print(psql.sqldf("select rank from df1 ",env=locals()))


#函数实现
df=pd.DataFrame({"姓名":["张飞","关羽","赵云","黄忠","典韦","典韦"],"语文":[66,95,95,90,80,80],"英语":[65,85,92,88,90,90],"数学":[None,98,96,77,90,90]})
df.drop_duplicates(inplace=True)
def sumFunc(srs):
    srs["sum"]=srs["语文"]+srs["英语"]+srs["数学"]
    return srs 
# df.fillna(df['数学'].mean(),inplace=True)
df.dropna(inplace=True)
df=df.apply(sumFunc,axis=1)
df.sort_values("sum",ascending=False,inplace=True)
print(df)

#sql实现
df2=pd.DataFrame({"姓名":["张飞","关羽","赵云","黄忠","典韦","典韦"],"语文":[66,95,95,90,80,80],"英语":[65,85,92,88,90,90],"数学":[None,98,96,77,90,90]})
result=psql.sqldf("select distinct 语文,英语,数学,语文+英语+数学 sum from df2 where 语文 is not null and 英语 is not null and 数学 is not null order by sum desc ",env=locals())
print(result)



